package edu.sdufe.dao;

import edu.sdufe.pojo.School;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

@Repository
public class SchoolJDBCDao {

    public List<School> jdbc1(String value) throws SQLException, ClassNotFoundException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取链接
        String url = "jdbc:mysql://localhost:3306/db01";
        String username = "root";
        String password = "12345";
        Connection connection = DriverManager.getConnection(url, username, password);
        //3.执行SQL
        Statement statement = connection.createStatement();
        String sql = "select * from school where position = '0' or '1' = '1'";
        ResultSet resultSet = statement.executeQuery(sql);
        //4.处理返回值
        List<School> schools = new ArrayList<>();
        while (resultSet.next()) {
            Integer id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String contacts = resultSet.getString("contact");
            String fullName = resultSet.getString("full_name");
            LocalDate foundDate = resultSet.getDate("found_date").toLocalDate();
            String category = resultSet.getString("category");
            LocalDateTime createTime = resultSet.getTimestamp("create_time").toLocalDateTime();
            LocalDateTime updateTime = resultSet.getTimestamp("update_time").toLocalDateTime();
            String position = resultSet.getString("position");
            School school = new School(id, name, contacts, fullName,foundDate,category,createTime,updateTime,position);
            schools.add(school);
        }
        //5.释放资源
        resultSet.close();
        statement.close();
        connection.close();

        return schools;
    }

    public List<School> jdbc2(String value) throws SQLException, ClassNotFoundException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取链接
        String url = "jdbc:mysql://localhost:3306/db01";
        String username = "root";
        String password = "12345";
        Connection connection = DriverManager.getConnection(url, username, password);
        //3.执行SQL
//        Statement statement = connection.createStatement();
//        String sql = "select * from school where position = '0' or '1' = '1'";
        String sql = "select * from school where position = ?";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, value);
        ResultSet resultSet = preparedStatement.executeQuery();
        //4.处理返回值
        List<School> schools = new ArrayList<>();
        while (resultSet.next()) {
            Integer id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String contacts = resultSet.getString("contact");
            String fullName = resultSet.getString("full_name");
            LocalDate foundDate = resultSet.getDate("found_date").toLocalDate();
            String category = resultSet.getString("category");
            LocalDateTime createTime = resultSet.getTimestamp("create_time").toLocalDateTime();
            LocalDateTime updateTime = resultSet.getTimestamp("update_time").toLocalDateTime();
            String position = resultSet.getString("position");
            School school = new School(id, name, contacts, fullName,foundDate,category,createTime,updateTime,position);
            schools.add(school);
        }
        //5.释放资源
        resultSet.close();
        preparedStatement.close();
        connection.close();

        return schools;
    }
}
